package bookDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import table.Book;

public class BookImpl implements BookDao {
	String sql = null;
	int result = 0;
	String sql1 = null;
	int result1 = 0;
	@Override
	public List<Book> find() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_name,book_writer,book_publishingHouse,callNumber,book_number,book_price,book_isbn FROM book";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) { 
				int id = rs.getInt(1);
				String bookName = rs.getString(2);
				String bookWriter = rs.getString(3);
				String bookPublishingHouse = rs.getString(4);
				String callNumber = rs.getString(5);// 索书号
				int bookNumber = rs.getInt(6);// 图书数量
				double bookPrice = rs.getDouble(7);
				String bookIsbn = rs.getString(8);// 图书编号
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}

	@Override
	public void save(Book x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "INSERT INTO book VALUE(null,?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, x.getBookName());
			ps.setString(2, x.getBookWriter());
			ps.setString(3, x.getBookPublishingHouse());
			ps.setString(4, x.getBookCallNumber());
			ps.setInt(5, x.getBookNumber());
			ps.setDouble(6, x.getBookPrice());		
			ps.setString(7, x.getBookIsbn());
			result = ps.executeUpdate(); 
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
	}

	@Override
	public void delete(int x,String y) {
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "DELETE FROM book WHERE book_id=?";
			sql1 = "DELETE FROM bookbarcode WHERE book_isbn=?";
			ps = con.prepareStatement(sql);
			ps1 = con.prepareStatement(sql1);
			ps.setInt(1, x);
			ps1.setString(1, y);
			result = ps.executeUpdate();
			result1 = ps1.executeUpdate();
			System.out.println("操作结果是" + result);
			System.out.println("操作结果是bookbarcode表" + result1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
			BookDbconnection.close(rs1, ps1);
		}

	}
	
	@Override
	public void update(Book x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "UPDATE book SET book_name=?,book_writer=?,book_publishingHouse=?,callNumber=?,book_number=?,book_price=?,book_isbn=? WHERE book_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x.getBookName()); 
			ps.setString(2, x.getBookWriter()); 
			ps.setString(3, x.getBookPublishingHouse());
			ps.setString(4, x.getBookCallNumber());
			ps.setInt(5, x.getBookNumber());
			ps.setDouble(6, x.getBookPrice());
			ps.setString(7, x.getBookIsbn()); 
			ps.setInt(8,x.getBookId());
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
	}
	/**
	 * 通过索书号、书名查找图书
	 */
	@Override
	public List<Book> get1(String x,String y) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_writer,book_publishingHouse,book_number,book_price,book_isbn,callNumber,book_name FROM book WHERE callNumber LIKE ? and book_name LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			ps.setString(2, '%'+y+'%');
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookWriter = rs.getString(2);
				String bookPublishingHouse = rs.getString(3);
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号
				String callNumber = rs.getString(7);
				String bookName = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}

	/**
	 * 通过书名、作者查找图书信息
	 */
	@Override
	public List<Book> get2(String y,String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,callNumber,book_number,book_price,book_isbn,book_name,book_writer FROM book WHERE book_name LIKE ? and book_writer LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+y+'%');
			ps.setString(2, '%'+z+'%');
			rs = ps.executeQuery(); 
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				String callNumber = rs.getString(3);// 索书号
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号
				String bookName = rs.getString(7);
				String bookWriter = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}

	/**
	 * 通过索书号、作者查找
	 */
	@Override
	public List<Book> get3(String x,String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,book_name,book_number,book_price,book_isbn,callNumber,book_writer FROM book WHERE callNumber LIKE ? and book_writer LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			ps.setString(2, '%'+z+'%');
			rs = ps.executeQuery(); 
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				String bookName = rs.getString(3);
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号
				String callNumber = rs.getString(7);
				String bookWriter = rs.getString(8);
				Book book = new Book(id, callNumber, bookWriter, bookPublishingHouse, bookName, bookNumber, bookPrice, bookIsbn);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}

	/**
	 *
	 *通过索书号、书名、作者查找图书
	 */
	@Override
	public List<Book> get4(String x, String y, String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,book_number,book_price,book_isbn,callNumber,book_name,book_writer FROM book WHERE callNumber LIKE ? and book_name LIKE ? and book_writer LIKE?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			ps.setString(2, '%'+y+'%');
			ps.setString(3, '%'+z+'%');
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				int bookNumber = rs.getInt(3);// 图书数量
				double bookPrice = rs.getDouble(4);
				String bookIsbn = rs.getString(5);// 图书编号
				String callNumber = rs.getString(6);
				String bookName = rs.getString(7);
				String bookWriter = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice, bookIsbn
						);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}
	//通过索书号查找图书
	@Override
	public List<Book> get5(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,book_name,book_number,book_price,book_isbn,book_writer,callNumber FROM book WHERE callNumber LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				String bookName = rs.getString(3);
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号 
				String bookWriter  = rs.getString(7);
				String callNumber  = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse,callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}
	//通过书名查找图书
	@Override
	public List<Book> get6(String y) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,callNumber,book_number,book_price,book_isbn,book_writer,book_name FROM book WHERE book_name LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+y+'%');
			rs = ps.executeQuery(); 
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				String callNumber = rs.getString(3);// 索书号
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号
				String bookWriter  = rs.getString(7);
				String bookName  = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}
	//通过作者查找图书
	@Override
	public List<Book> get7(String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> BiList = new ArrayList<Book>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id,book_publishingHouse,callNumber,book_number,book_price,book_isbn,book_name,book_writer FROM book WHERE book_writer LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+z+'%');
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String bookPublishingHouse = rs.getString(2);
				String callNumber = rs.getString(3);// 索书号
				int bookNumber = rs.getInt(4);// 图书数量
				double bookPrice = rs.getDouble(5);
				String bookIsbn = rs.getString(6);// 图书编号
				String bookName  = rs.getString(7);
				String bookWriter  = rs.getString(8);
				Book book = new Book(id, bookName, bookWriter, bookPublishingHouse, callNumber, bookNumber, bookPrice,
						bookIsbn);
				BiList.add(book);
 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return BiList;
	}

	@Override
	public int getBookId(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int bookId = 0;
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT book_id FROM book WHERE book_isbn = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) {
				bookId = rs.getInt(1);
				} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return bookId;
	}

}